/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTROLLERS;

import BEANS.ComboItem;
import BEANS.Product;
import UTIL.DatabaseConnector;
import UTIL.InputValidator;
import UTIL.TableManager;
import VIEW.NewReturns;
import VIEW.ProductsUI;
import datechooser.beans.DateChooserCombo;
import java.awt.Color;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.swing.JDialog;
import java.util.Date;
import java.util.GregorianCalendar;
import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;

/**
 *
 * @author Belthazod
 */
public class ReplacementController {
    private DatabaseConnector dbConnector = DatabaseConnector.getInstance();
    private TableManager receiptTableManager;
    private TableManager replacementTableManager;
    private TableManager productsTableManager;
    private JScrollPane productsTableScrollPane;
    private JScrollPane receiptTableScrollPane;
    private JTextField receiptNumberInput;
    private JLabel salesDateText;
    private JLabel salesDateLabel;
    private JLabel salesIDLabel;
    private JLabel receiptNumberText;
    private JLabel receiptNumberLabel;
    private JLabel dateReplacedText;
    private DateChooserCombo dateReplacedDateChooser;
    private JLabel remarksText;
    private JTextArea remarksTextArea;
    private JScrollPane remarksTextAreaScrollPane;
    private JDialog inputQuantityDialog;
    private JTextField inputQuantityTextField;
    private JLabel receiptTableHint;
    private JButton acceptButton;
    private JTextField searchInput;
    private JLabel clearMark;
    private ProductController productController;
    private JComboBox<ComboItem> categoryFilterComboBox;
    private JComboBox<ComboItem> supplierFilterComboBox;
    private JDialog changeSerialNumberDialog;
    private JLabel warrantySalesDate;
    private JLabel warrantyReplacementDate;
    private JLabel warrantyDuration;
    private JLabel warrantyStatus;
    private JLabel enterSerialNumberText;
    private JButton warrantyOKButton;
    private JTextField newSerialNumberInput;
    private JLabel numerousQuantityText;
    private ButtonGroup defectiveButtonGroup;
    private JRadioButton defectiveRadioButton;
    private JRadioButton notDefectiveRadioButton;
    private JLabel theseItemsAreText;
    private JLabel enterQuantityText;
    private JButton searchButton;
    private JLabel clear;
    private DateChooserCombo dateFrom;
    private DateChooserCombo dateTo;
    
    public ReplacementController(JTextField receiptNumberInput, 
            JTable receiptTable, JLabel receiptTableHint, 
            JScrollPane receiptTableScrollPane, JLabel salesDateText, 
            JLabel salesDateLabel, JLabel salesIDLabel, 
            JLabel receiptNumberText,  JLabel receiptNumberLabel, 
            JLabel dateReplacedText, DateChooserCombo dateReplacedDateChooser, 
            JLabel remarksText, JScrollPane remarksTextAreaScrollPane, 
            JTextArea remarksTextArea, JScrollPane productsTableScrollPane, 
            JTable productsTable, JTable replacementTable,  
            JDialog inputQuantityDialog, JTextField inputQuantityTextField, 
            JButton acceptButton, JLabel clearMark, JTextField searchInput, 
            JComboBox<ComboItem> categoryFilterComboBox, 
            JComboBox<ComboItem> supplierFilterComboBox, 
            JDialog changeSerialNumberDialog, JLabel warrantySalesDate, 
            JLabel warrantyReplacementDate, JLabel warrantyDuration, 
            JLabel warrantyStatus, JLabel enterSerialNumberText, 
            JTextField newSerialNumberInput, JButton warrantyOKButton,
            JLabel numerousQuantityText, ButtonGroup defectiveButtonGroup,
            JRadioButton defectiveRadioButton, 
            JRadioButton notDefectiveRadioButton, JLabel theseItemsAreText,
            JLabel enterQuantityText, JButton searchButton, JLabel clear,
            DateChooserCombo dateFrom, DateChooserCombo dateTo){
        this.dateFrom = dateFrom;
        this.dateTo = dateTo;
        this.receiptTableManager = new TableManager(receiptTable);
        this.receiptTableScrollPane = receiptTableScrollPane;
        this.salesDateText = salesDateText;
        this.salesDateLabel = salesDateLabel;
        this.salesIDLabel = salesIDLabel;
        this.receiptNumberText = receiptNumberText;
        this.receiptNumberLabel = receiptNumberLabel;
        this.dateReplacedText = dateReplacedText;
        this.dateReplacedDateChooser = dateReplacedDateChooser;
        this.remarksText = remarksText;
        this.remarksTextArea = remarksTextArea;
        this.remarksTextAreaScrollPane = remarksTextAreaScrollPane;
        this.replacementTableManager = new TableManager(replacementTable);
        this.inputQuantityDialog = inputQuantityDialog;
        this.inputQuantityTextField = inputQuantityTextField;
        this.receiptNumberInput = receiptNumberInput;
        this.receiptTableHint = receiptTableHint;
        this.acceptButton = acceptButton;
        this.productsTableManager = new TableManager(productsTable);
        this.productsTableScrollPane = productsTableScrollPane;
        this.clearMark = clearMark;
        this.searchInput = searchInput;
        this.categoryFilterComboBox = categoryFilterComboBox;
        this.supplierFilterComboBox = supplierFilterComboBox;
        this.changeSerialNumberDialog = changeSerialNumberDialog;
        this.warrantySalesDate = warrantySalesDate;
        this.warrantyReplacementDate = warrantyReplacementDate;
        this.warrantyDuration = warrantyDuration;
        this.warrantyStatus = warrantyStatus;
        this.enterSerialNumberText = enterSerialNumberText;
        this.warrantyOKButton = warrantyOKButton;
        this.newSerialNumberInput = newSerialNumberInput;
        this.numerousQuantityText = numerousQuantityText;
        this.defectiveButtonGroup = defectiveButtonGroup;
        this.defectiveRadioButton = defectiveRadioButton;
        this.notDefectiveRadioButton = notDefectiveRadioButton;
        this.theseItemsAreText = theseItemsAreText;
        this.enterQuantityText = enterQuantityText;
        this.searchButton = searchButton;
        this.clear = clear;
        
        productController = new ProductController();
        
        clearMark.setVisible(false);
        receiptTableManager.hideColumn(0);
        receiptTableManager.alignCellRight(2);
        productsTableManager.hideColumn(0);
        productsTableManager.alignCellRight(4);
        salesIDLabel.setVisible(false);
    }
    
    public void searchReceiptNumber(){
        try{
            DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
            String receiptNumberValue = receiptNumberInput.getText();
            ResultSet rs = dbConnector.query("SELECT sales_details.sales_id, sales_date, product_id, name, quantity_sold, receipt_number, serial_number FROM sales_details JOIN product USING(product_id) JOIN sales USING(sales_id) WHERE sales.receipt_number = ?", receiptNumberValue);
            
            
                receiptTableManager.clearTableContents();
                int count = 0;
                while(rs.next()){
                    String salesDate = rs.getString(2).substring(0,10);
                    String salesID = rs.getString(1);
                    String receiptNumber = rs.getString(5);
                    
                    if(count == 0){
                        Date date = dfto.parse(salesDate);
                        String dateOfSale = sdf.format(date);
                        salesDateLabel.setText(dateOfSale);
                        salesIDLabel.setText(salesID);
                        receiptNumberLabel.setText(receiptNumber);
                        count++;
                    }
                    String productID = rs.getString(3);
                    String productName = rs.getString(4);
                    String quantitySold = rs.getString(5);
                    String serialNumber = rs.getString(7);
                    receiptTableManager.addRowContent(new String[]{productID, productName, serialNumber, quantitySold});
                }
                if(count > 0){
                if(receiptNumberValue.equals("")){
                    clear.setVisible(false);
                }else{
                    clear.setVisible(true);
                }
                salesDateLabel.setVisible(true);
                receiptTableScrollPane.setVisible(true);
                remarksTextAreaScrollPane.setVisible(true);
                salesDateText.setVisible(true);
                receiptNumberText.setVisible(true);
                dateReplacedText.setVisible(true);
                dateReplacedDateChooser.setVisible(true);
            }else{
                JOptionPane.showMessageDialog(null, "No receipt number found! Please enter an existing receipt number.", "Receipt Not found", JOptionPane.ERROR_MESSAGE);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(ParseException pe){
            pe.printStackTrace();
        }
    }
    
    public void clear(){
        receiptNumberInput.setText("");
        clear.setVisible(false);
    }
    
    public void updateReplacementTable(){
        try{
            ResultSet rs = dbConnector.query("SELECT rep_date, t1.name, t2.name, remarks, quantity FROM product t1 JOIN replacements USING(product_id) LEFT JOIN product t2 ON t2.product_id = replacements.replacement_product_id ORDER BY 1");
            replacementTableManager.clearTableContents();
            DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
            while(rs.next()){
                String replacementDate = rs.getString(1).substring(0, 10);
                String productName = rs.getString(2);
                String replacementProductName = rs.getString(3);
                String remarks = rs.getString(4);
                String quantity = rs.getString(5);
                Date date = dfto.parse(replacementDate);
                String dateOfSale = sdf.format(date);
                
                replacementTableManager.addRowContent(new String[]{dateOfSale, productName, replacementProductName, quantity, remarks});
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void acceptItemWithWarranty(){
        try{
            String salesID = salesIDLabel.getText();
            String productID = receiptTableManager.getIDFromTable(receiptTableManager.getSelectedRow());
            String productName = receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(),1);
            String dateInput = dateReplacedDateChooser.getText().split(",")[1].substring(1).concat(","+ dateReplacedDateChooser.getText().split(",")[2]);
            String remarks = remarksTextArea.getText();
            String oldSerialNumber = receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 2);
            String newSerialNumber = newSerialNumberInput.getText();
            
            ResultSet supplierIDRS = dbConnector.query("SELECT supplier_id FROM supplier JOIN product USING(supplier_id) WHERE product_id = ?",productID);
            supplierIDRS.next();
            String oldItemSupplierID = supplierIDRS.getString(1);
            DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            supplierIDRS.close();
            Date date = dffrom.parse(dateInput);
            String replacementDate = sdf.format(date);
            
            
            
            //product 2
            String replacementProductID = productsTableManager.getIDFromTable(productsTableManager.getSelectedRow());
            String replacementQuantity = productsTableManager.getValueAt(productsTableManager.getSelectedRow(), 4);
            String replacementProductName = productsTableManager.getValueAt(productsTableManager.getSelectedRow(), 1);
            int repQty = Integer.parseInt(replacementQuantity);
            int response = JOptionPane.showConfirmDialog(null, "The product " + productName+" will be replaced with the product " + replacementProductName + ". Continue?", "Confirm replacement", JOptionPane.YES_NO_OPTION);
            if(response == 0){
                dbConnector.insert("INSERT INTO replacements(sales_id, product_id, rep_date, remarks, replacement_product_id, quantity) VALUES (?,?,?,?,?,?)", new String[]{salesID, productID, replacementDate, remarks, replacementProductID, "1"});
                ResultSet replacementIDRS = dbConnector.query("SELECT replacement_id FROM replacements ORDER BY 1 DESC");
                replacementIDRS.next();
                
                String replacementID = replacementIDRS.getString(1);
                replacementIDRS.close();
                dbConnector.update("UPDATE product SET physical_count = physical_count -1 WHERE product_id = ?", new String[]{replacementProductID});
                dbConnector.insert("INSERT INTO returns(supplier_id, product_id, replacement_id, serial_number) VALUES(?,?,?,?)", new String[]{oldItemSupplierID, productID, replacementID, oldSerialNumber});
                dbConnector.update("UPDATE sales_details SET serial_Number = ? WHERE sales_id = ? AND product_id = ?", new String[]{newSerialNumber, salesID, productID});
                JOptionPane.showMessageDialog(null, "The product " +productName+ " was replaced with " + replacementProductName, "Replacement successful", JOptionPane.INFORMATION_MESSAGE);

                newSerialNumberInput.setText("");
                remarksTextArea.setText("");
                changeSerialNumberDialog.dispose();
                updateReplacementTable();
                NewReturns.filterComboBox();
            }
        }catch(NullPointerException npe){
            npe.printStackTrace();
            JOptionPane.showMessageDialog(null, "No product is being accepted.", "Input Error", JOptionPane.ERROR_MESSAGE);
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(ParseException pe){
            pe.printStackTrace();
        }catch(IndexOutOfBoundsException ioobe){
            JOptionPane.showMessageDialog(null, "Please select an product from the table above and select another product from the products table below and click on the 'Accept product and replace with' button to indicate a replacement.", "No product selected",JOptionPane.ERROR_MESSAGE);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void acceptItem(){
        try{
            String salesID = salesIDLabel.getText();
            String productID = receiptTableManager.getIDFromTable(receiptTableManager.getSelectedRow());
            String productName = receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(),1);
            String dateInput = dateReplacedDateChooser.getText().split(",")[1].substring(1).concat(","+ dateReplacedDateChooser.getText().split(",")[2]);
            String remarks = remarksTextArea.getText();
            int productQuantity = Integer.parseInt(receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 3));
            ResultSet supplierIDRS = dbConnector.query("SELECT supplier_id FROM supplier JOIN product USING(supplier_id) WHERE product_id = ?",productID);
            supplierIDRS.next();
            String oldItemSupplierID = supplierIDRS.getString(1);
            supplierIDRS.close();
            DateFormat dffrom = new SimpleDateFormat("MMMMM d, yyyy");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            Date date = dffrom.parse(dateInput);
            String replacementDate = sdf.format(date);

            //product 2
            String replacementProductID = productsTableManager.getIDFromTable(productsTableManager.getSelectedRow());
            String replacementQuantity = productsTableManager.getValueAt(productsTableManager.getSelectedRow(), 4);
            String replacementProductName = productsTableManager.getValueAt(productsTableManager.getSelectedRow(), 1);
            int repQty = Integer.parseInt(replacementQuantity);
            if(productID.equals(replacementProductID) && productQuantity == 1){
                int response = JOptionPane.showConfirmDialog(null, "The product " + productName+" will be replaced with the product " + replacementProductName + ". Continue?", "Confirm replacement", JOptionPane.YES_NO_OPTION);
                    if(response == 0){
                dbConnector.insert("INSERT INTO replacements(sales_id, product_id, rep_date, remarks, replacement_product_id, quantity) VALUES (?,?,?,?,?,?)", new String[]{salesID, productID, replacementDate, remarks, replacementProductID, replacementQuantity});
                        ResultSet replacementIDRS = dbConnector.query("SELECT replacement_id FROM replacements ORDER BY 1 DESC");
                        replacementIDRS.next();
                        String replacementID = replacementIDRS.getString(1);
                        replacementIDRS.close();
                dbConnector.insert("INSERT INTO returns(supplier_id, product_id, replacement_id, return_quantity) VALUES(?,?,?,?)", new String[]{oldItemSupplierID, productID, replacementID, "1"});
                dbConnector.update("UPDATE product SET physical_count = physical_count - 1 WHERE product_id = ?", new String[]{replacementProductID});
                        ProductsUI.updateAdminProductsTable();
                        sortIfHasWarranty();
                        searchReceiptNumber();
                        JOptionPane.showMessageDialog(null, "The product " + productName + " was replaced.", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                        NewReturns.filterComboBox();
                    }
            }else if(inputQuantityTextField.isVisible() && defectiveRadioButton.isVisible()){
                int quantity = Integer.parseInt(inputQuantityTextField.getText());
                
                if(quantity > 0 && quantity <=repQty && quantity <= productQuantity){
                    int response = JOptionPane.showConfirmDialog(null, "The product " + productName+" will be replaced with the product " + replacementProductName + ". Continue?", "Confirm replacement", JOptionPane.YES_NO_OPTION);
                    if(response == 0){
                        dbConnector.insert("INSERT INTO replacements(sales_id, product_id, rep_date, remarks, replacement_product_id, quantity) VALUES (?,?,?,?,?,?)", new String[]{salesID, productID, replacementDate, remarks, replacementProductID, replacementQuantity});
                        ResultSet replacementIDRS = dbConnector.query("SELECT replacement_id FROM replacements ORDER BY 1 DESC");
                        replacementIDRS.next();
                        String replacementID = replacementIDRS.getString(1);
                        replacementIDRS.close();
                        if(defectiveRadioButton.isSelected()){
                            dbConnector.insert("INSERT INTO returns(supplier_id, product_id, replacement_id, return_quantity) VALUES(?,?,?,?)", new String[]{oldItemSupplierID, productID, replacementID, Integer.toString(quantity)});

                        }else{
                            dbConnector.update("UPDATE product SET physical_count = physical_count + ? WHERE product_id = ?", new String[]{Integer.toString(quantity), productID});
                        }
                        dbConnector.update("UPDATE product SET physical_count = physical_count - ? WHERE product_id = ?", new String[]{Integer.toString(quantity), replacementProductID});
                        if(quantity == productQuantity){
                            
                            ResultSet salesTypeRS = dbConnector.query("SELECT sales_type FROM sales_details where sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            salesTypeRS.next();
                            String salesType = salesTypeRS.getString(1);
                            salesTypeRS.close();
                            dbConnector.delete("DELETE FROM sales_details WHERE sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            ResultSet existsRS = dbConnector.query("SELECT product_id FROM sales_details WHERE sales_ID = ? AND product_id = ?", new String[]{salesID, replacementProductID});
                            
                            if(existsRS.next()){
                                dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold + ? WHERE sales_id = ? AND product_id = ?", new String[]{Integer.toString(quantity), salesID, replacementProductID});
                            }else{
                                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type) VALUES(?,?,?,?)", new String[]{salesID, replacementProductID, Integer.toString(quantity), salesType});
                            }
                            existsRS.close();
                            
                            
                            JOptionPane.showMessageDialog(null,"(" + quantity + ") " + productName + "(s) was replaced with " + repQty + " " + replacementProductName + ".", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                        }else{
                            ResultSet salesTypeRS = dbConnector.query("SELECT sales_type FROM sales_details where sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            salesTypeRS.next();
                            String salesType = salesTypeRS.getString(1);
                            salesTypeRS.close();
                            dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold - ? WHERE product_id = ? AND sales_id = ?", new String[]{Integer.toString(quantity), productID, salesID});
                            ResultSet existsRS = dbConnector.query("SELECT product_id FROM sales_details WHERE sales_ID = ? AND product_id = ?", new String[]{salesID, replacementProductID});
                            
                            if(existsRS.next()){
                                dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold + ? WHERE sales_id = ? AND product_id = ?", new String[]{Integer.toString(quantity), salesID, replacementProductID});
                            }else{
                                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type) VALUES(?,?,?,?)", new String[]{salesID, replacementProductID, Integer.toString(quantity), salesType});
                            }
                            existsRS.close();
                            JOptionPane.showMessageDialog(null,"(" + quantity + ") " + productName + "(s) was replaced with " + repQty + " " + replacementProductName + ".", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                            NewReturns.filterComboBox();
                        }
                        //dbConnector.update("UPDATE sales_details SET serial_Number = ? WHERE sales_id = ? AND product_id = ?", new String[]{newSerialNumber, salesID, productID});
                        //JOptionPane.showMessageDialog(null, "The product " +productName+ " was replaced with " + replacementProductName, "Replacement successful", JOptionPane.INFORMATION_MESSAGE);

                        inputQuantityTextField.setText("");
                        inputQuantityDialog.dispose();
                        remarksTextArea.setText("");
                        ProductsUI.updateAdminProductsTable();
                        sortIfHasWarranty();
                        searchReceiptNumber();
                        NewReturns.filterComboBox();
                    }
                }else if(quantity < 0){
                    JOptionPane.showMessageDialog(null, "The quantity cannot be negative. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity == 0){
                    JOptionPane.showMessageDialog(null, "The quantity cannot be zero. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity > repQty){
                    JOptionPane.showMessageDialog(null, "The quantity you have entered exceeds the available stock of " + replacementProductName + ". Please check the quantity entered.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity >productQuantity){
                    JOptionPane.showMessageDialog(null, "The quantity you have entered exceeds the product's quantity in the receipt. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else{
                    JOptionPane.showMessageDialog(null, "The quantity you have entered is not a valid quantity. Please input the appropriate quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }
            }else if(inputQuantityTextField.isVisible()){
                int quantity = Integer.parseInt(inputQuantityTextField.getText());
                if(quantity > 0 && quantity <=repQty && quantity <= productQuantity){
                    int response = JOptionPane.showConfirmDialog(null, "The product " + productName+" will be replaced with the product " + replacementProductName + ". Continue?", "Confirm replacement", JOptionPane.YES_NO_OPTION);
                    if(response == 0){
                        dbConnector.insert("INSERT INTO replacements(sales_id, product_id, rep_date, remarks, replacement_product_id, quantity) VALUES (?,?,?,?,?,?)", new String[]{salesID, productID, replacementDate, remarks, replacementProductID, Integer.toString(quantity)});
                        ResultSet replacementIDRS = dbConnector.query("SELECT replacement_id FROM replacements ORDER BY 1 DESC");
                        replacementIDRS.next();
                        String replacementID = replacementIDRS.getString(1);
                        replacementIDRS.close();
                        dbConnector.insert("INSERT INTO returns(supplier_id, product_id, replacement_id, return_quantity) VALUES(?,?,?,?)", new String[]{oldItemSupplierID, productID, replacementID, Integer.toString(quantity)});               
                        dbConnector.update("UPDATE product SET physical_count = physical_count - ? WHERE product_id = ?", new String[]{Integer.toString(quantity), replacementProductID});
                        if(quantity == productQuantity){
                            ResultSet salesTypeRS = dbConnector.query("SELECT sales_type FROM sales_details where sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            salesTypeRS.next();
                            String salesType = salesTypeRS.getString(1);
                            salesTypeRS.close();
                            dbConnector.delete("DELETE FROM sales_details WHERE sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            
                            ResultSet existsRS = dbConnector.query("SELECT product_id FROM sales_details WHERE sales_ID = ? AND product_id = ?", new String[]{salesID, replacementID});
                            if(existsRS.next()){
                                dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold + ? WHERE sales_id = ? AND product_id = ?", new String[]{Integer.toString(quantity), salesID, replacementProductID});
                            }else{
                                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type) VALUES(?,?,?,?)", new String[]{salesID, replacementProductID, Integer.toString(quantity), salesType});
                            }
                            existsRS.close();
                            JOptionPane.showMessageDialog(null,"(" + quantity + ") " + productName + "(s) was/were replaced.", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                        }else{
                            ResultSet salesTypeRS = dbConnector.query("SELECT sales_type FROM sales_details where sales_ID = ? AND product_ID = ?", new String[]{salesID, productID});
                            salesTypeRS.next();
                            String salesType = salesTypeRS.getString(1);
                            salesTypeRS.close();
                            dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold - ? WHERE sales_ID = ? AND product_ID = ?", new String[]{Integer.toString(quantity), salesID, productID});
                            ResultSet existsRS = dbConnector.query("SELECT product_id FROM sales_details WHERE sales_ID = ? AND product_id = ?", new String[]{salesID, replacementID});
                            if(existsRS.next()){
                                dbConnector.update("UPDATE sales_details SET quantity_sold = quantity_sold + ? WHERE sales_id = ? AND product_id = ?", new String[]{Integer.toString(quantity), salesID, replacementProductID});
                            }else{
                                dbConnector.insert("INSERT INTO sales_details(sales_id, product_id, quantity_sold, sales_type) VALUES(?,?,?,?)", new String[]{salesID, replacementProductID, Integer.toString(quantity), salesType});
                            }
                            existsRS.close();
                            JOptionPane.showMessageDialog(null,"(" + quantity + ") " + productName + "(s) was/were replaced.", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                        }
                        //dbConnector.update("UPDATE sales_details SET serial_Number = ? WHERE sales_id = ? AND product_id = ?", new String[]{newSerialNumber, salesID, productID});
                        //JOptionPane.showMessageDialog(null, "The product " +productName+ " was replaced with " + replacementProductName, "Replacement successful", JOptionPane.INFORMATION_MESSAGE);

                        inputQuantityTextField.setText("");
                        inputQuantityDialog.dispose();
                        remarksTextArea.setText("");
                        ProductsUI.updateAdminProductsTable();
                        sortIfHasWarranty();
                        searchReceiptNumber();
                        NewReturns.filterComboBox();
                    }
                }else if(quantity < 0){
                    JOptionPane.showMessageDialog(null, "The quantity cannot be negative. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity == 0){
                    JOptionPane.showMessageDialog(null, "The quantity cannot be zero. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity > repQty){
                    JOptionPane.showMessageDialog(null, "The quantity you have entered exceeds the available stock of " + replacementProductName + ". Please check the quantity entered.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else if(quantity >productQuantity){
                    JOptionPane.showMessageDialog(null, "The quantity you have entered exceeds the product's quantity in the receipt. Please input a valid quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }else{
                    JOptionPane.showMessageDialog(null, "The quantity you have entered is not a valid quantity. Please input the appropriate quantity.", "Invalid quantity", JOptionPane.ERROR_MESSAGE);
                }
            }else if(defectiveRadioButton.isVisible()){
                int response = JOptionPane.showConfirmDialog(null, "The product " + productName+" will be replaced with the product " + replacementProductName + ". Continue?", "Confirm replacement", JOptionPane.YES_NO_OPTION);
                if(response == 0){
                    dbConnector.insert("INSERT INTO replacements(sales_id, product_id, rep_date, remarks, replacement_product_id, quantity) VALUES (?,?,?,?,?,?)", new String[]{salesID, productID, replacementDate, remarks, replacementProductID, replacementQuantity});
                    ResultSet replacementIDRS = dbConnector.query("SELECT replacement_id FROM replacements ORDER BY 1 DESC");
                    replacementIDRS.next();
                    String replacementID = replacementIDRS.getString(1);
                    replacementIDRS.close();
                    if(defectiveRadioButton.isSelected()){
                        dbConnector.insert("INSERT INTO returns(supplier_id, product_id, replacement_id) VALUES(?,?,?)", new String[]{oldItemSupplierID, productID, replacementID});
                        dbConnector.update("UPDATE product SET physical_count = physical_count - 1 WHERE product_id = ?", new String[]{replacementProductID});
                    }else{
                        dbConnector.update("UPDATE product SET physical_count = physical_count + 1 WHERE product_id = ?", new String[]{productID});
                        dbConnector.update("UPDATE product SET physical_count = physical_count - 1 WHERE product_id = ?", new String[]{replacementProductID});
                    }
                    inputQuantityDialog.dispose();
                    remarksTextArea.setText("");
                    JOptionPane.showMessageDialog(null, "The product " + productName + " was replaced.", "Replacement successful", JOptionPane.INFORMATION_MESSAGE);
                    ProductsUI.updateAdminProductsTable();
                        sortIfHasWarranty();
                        searchReceiptNumber();
                        NewReturns.filterComboBox();
                }
                
            }

        }catch(NullPointerException npe){
            npe.printStackTrace();
            JOptionPane.showMessageDialog(null, "No product is being accepted.", "Input Error", JOptionPane.ERROR_MESSAGE);
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(ParseException pe){
            pe.printStackTrace();
        }catch(IndexOutOfBoundsException ioobe){
            JOptionPane.showMessageDialog(null, "Please select an product to replace.", "No product selected",JOptionPane.ERROR_MESSAGE);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void validateReplacementInfo(){
        try{
            receiptTableManager.getSelectedRow();
            try{
                DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
                SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
                String replacedProductID = receiptTableManager.getIDFromTable(receiptTableManager.getSelectedRow());
                String replacementProductID = productsTableManager.getIDFromTable(productsTableManager.getSelectedRow());
                    
                
                String productID = receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 0);
                ResultSet rs = dbConnector.query("SELECT sales_date, warranty_duration FROM sales JOIN sales_details USING(sales_id) WHERE sales_id = ? AND product_id = ?", new String[]{salesIDLabel.getText(),productID});
                rs.next();
                Date salesDate = rs.getTimestamp(1);
                int warrantyDuration = rs.getInt(2);
                int replacementQuantity = Integer.parseInt(receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 3));
                String inventoryProductName = productsTableManager.getValueAt(productsTableManager.getSelectedRow(), 1);
                int productQuantity = Integer.parseInt(productsTableManager.getValueAt(productsTableManager.getSelectedRow(),4));
                if(productQuantity>0){
                    if(warrantyDuration>0){
                        Calendar fromSalesDate = new GregorianCalendar();
                        Calendar toSalesDate = new GregorianCalendar();
                        Calendar replacementDate = dateReplacedDateChooser.getCurrent();

                        fromSalesDate.setTime(salesDate);
                        toSalesDate.setTime(salesDate);

                        toSalesDate.add(Calendar.MONTH, warrantyDuration);
                        long fromDate = fromSalesDate.getTimeInMillis();
                        long toDate = toSalesDate.getTimeInMillis();
                        long replacementTime = replacementDate.getTimeInMillis();

                        if(replacementTime >= fromDate && replacementTime <= toDate ){
                            warrantyStatus.setText("The product is still covered by warranty.");

                            enterSerialNumberText.setVisible(true);
                            newSerialNumberInput.setVisible(true);
                            warrantyOKButton.setEnabled(true);
                        }else{
                            warrantyStatus.setText("The product is no longer covered by warranty.");

                            enterSerialNumberText.setVisible(false);
                            newSerialNumberInput.setVisible(false);
                            warrantyOKButton.setEnabled(false);
                        }
                        Date date = dfto.parse(salesDate.toString());
                        String dateOfSale = sdf.format(date);
                        warrantySalesDate.setText(dateOfSale);
                        this.warrantyDuration.setText(Integer.toString(warrantyDuration) + " months");
                        warrantyReplacementDate.setText(dateReplacedDateChooser.getText());
                        changeSerialNumberDialog.setVisible(true);
                    }else if(replacementQuantity > 1){
                        numerousQuantityText.setText("The product being replaced has a total quantity of " + replacementQuantity + ".");
                        numerousQuantityText.setVisible(true);
                        inputQuantityTextField.setVisible(true);
                        enterQuantityText.setVisible(true);
                        inputQuantityDialog.setTitle("Multiple products detected");
                        if(replacedProductID.equals(replacementProductID)){
                            defectiveRadioButton.setVisible(false);
                            notDefectiveRadioButton.setVisible(false);
                            theseItemsAreText.setVisible(false);
                            defectiveButtonGroup.clearSelection();
                            inputQuantityDialog.setBounds(0, 0, 420, 160);
                            inputQuantityDialog.setLocationRelativeTo(null);
                        }else{
                            defectiveRadioButton.setVisible(true);
                            notDefectiveRadioButton.setVisible(true);
                            defectiveRadioButton.doClick();
                            theseItemsAreText.setVisible(true);
                            inputQuantityDialog.setBounds(0, 0, 420, 180);
                            inputQuantityDialog.setLocationRelativeTo(null);
                        }

                        inputQuantityDialog.setVisible(true);
                    }else{
                        if(replacedProductID.equals(replacementProductID)){
                            acceptItem();
                        }else{
                            numerousQuantityText.setVisible(false);
                            theseItemsAreText.setText("The product being replaced is:");
                            defectiveRadioButton.setVisible(true);
                            notDefectiveRadioButton.setVisible(true);
                            enterQuantityText.setVisible(false);
                            defectiveRadioButton.doClick();
                            inputQuantityTextField.setVisible(false);
                            inputQuantityDialog.setVisible(true);
                            inputQuantityDialog.setTitle("Replaced Product Status");
                            inputQuantityDialog.setBounds(0,0, 420, 100);
                            inputQuantityDialog.setLocationRelativeTo(null);
                        }
                    }
                }else{
                    JOptionPane.showMessageDialog(null, "There is no available stock for " + inventoryProductName+ ".", "Replacement failed.", JOptionPane.ERROR_MESSAGE);
                }
            }catch(NullPointerException npe){
                npe.printStackTrace();
                acceptItem();
            }catch(IndexOutOfBoundsException ioobe){
                JOptionPane.showMessageDialog(null, "Please select a product to replace.", "No item selected", JOptionPane.ERROR_MESSAGE);
            }catch(Exception e){
                e.printStackTrace();
            }
        }catch(IndexOutOfBoundsException ioobe){
            ioobe.printStackTrace();
            JOptionPane.showMessageDialog(null, "Please select a product to replace.", "No product selected for replacement", JOptionPane.ERROR_MESSAGE);
        }catch(Exception e){
            e.printStackTrace();
        }
        
        
    }
    
    public void sortIfHasWarranty(){
        ArrayList<Product> productList = productController.getProducts();
        try{
        if(!receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 2).equals("")){
            categoryFilterComboBox.setEnabled(false);
            supplierFilterComboBox.setEnabled(false);
            searchButton.setEnabled(false);
            for(Product product:productList){
                String productID = receiptTableManager.getValueAt(receiptTableManager.getSelectedRow(), 0);
                if(product.getProductID().equals(productID)){
                    productsTableManager.clearTableContents();
                    productsTableManager.addRowContent(new String[]{product.getProductID(), product.getName(), product.getSupplierName(),  product.getTypeName(), product.getPhysicalCount()});
                }
            }    
        }
        }catch(NullPointerException npe){
            searchButton.setEnabled(true);
            categoryFilterComboBox.setEnabled(true);
            supplierFilterComboBox.setEnabled(true);
            filterProducts();
        }
    }
    public void filterProducts(){
         ArrayList<Product> productList = productController.getProducts();
         ArrayList<Integer> coloredRows = new ArrayList<Integer>();
         if(categoryFilterComboBox.getSelectedItem()!=null && productList != null && supplierFilterComboBox.getSelectedItem()!=null){
            productsTableManager.clearTableContents();
            ArrayList<Product> filteredByCategoryList = filterByCategory( productList);
            ArrayList<Product> filteredByCategoryAndSupplierList = filterBySupplier(filteredByCategoryList);
            
            if(clearMark.isVisible()){
                searchProduct();
            }else{
                int rowCount = 0;
                for(Product product:filteredByCategoryAndSupplierList){
                        if(Integer.parseInt(product.getWarranty())==0){
                            String[] productValues = {product.getProductID(),
                            product.getName(), 
                            product.getSupplierName(),
                            product.getTypeName(),
                            product.getPhysicalCount()};
                        rowCount++;
                        productsTableManager.addRowContent(productValues);
                        if(Integer.parseInt(product.getPhysicalCount()) <= Integer.parseInt(product.getReorderQuantityLevel())){
                            //coloredRows.add(inventoryTableManager.getRowCount()-1);
                            coloredRows.add(rowCount);
                        }
                        }
                }
                //productsTableManager.setRowStatusColor(coloredRows, Color.PINK);
                
            }
         }        
    }
     
     public ArrayList<Product> filterByCategory(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(categoryFilterComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(categoryFilterComboBox.getSelectedItem().toString().equals(product.getTypeName())){
                    filteredProductList.add(product);
                }
            }
            return filteredProductList;
         }
     }
     public ArrayList<Product> filterBySupplier(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(supplierFilterComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(supplierFilterComboBox.getSelectedItem().toString().equals(product.getSupplierName())){
                    filteredProductList.add(product);
                    
                }
            }
            return filteredProductList;
         }
     }
     
     public void searchProduct(){
        String productName = searchInput.getText();
        
        ArrayList<Product> productList = productController.getProducts();
        productsTableManager.clearTableContents();
        
        ArrayList<Product> filteredByCategoryList = filterByCategory(productList);
        ArrayList<Product> filteredBySupplierAndCategoryList = filterBySupplier(filteredByCategoryList);
        for(Product product: filteredBySupplierAndCategoryList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getTypeName(),
                        product.getSupplierName(),
                        product.getPhysicalCount()};
                productsTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
    }
    
    public void clearSearch(){
        searchInput.setText("");
        searchProduct();
        clearMark.setVisible(false);
    }
    
    public void clearList(){
        int result = JOptionPane.showConfirmDialog(null, "Are you sure you want to clear the list?", "Confirm Checkout", JOptionPane.YES_NO_OPTION);
        if(result == 0){
            receiptTableManager.clearTableContents();
        }
    }
    
    public void replacementReport(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT rep_date, T1.name, T2.name, replacements.remarks, receipt_number, quantity FROM replacements JOIN product T1 USING(product_id) JOIN product T2 ON replacements.replacement_product_id = T2.product_id JOIN sales USING(sales_id) WHERE rep_date between '" + toDate + "' and '" + fromDate + "' ORDER BY 1 DESC");

            while(rs.next()){
                String date = rs.getString("rep_date");
                Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString(2);
                String quantity = rs.getString("quantity");
                String description = rs.getString(3);
                String remarks = rs.getString(4);
                //String value2 = rs.getString("old_item_status");
                String value1 = rs.getString("receipt_number");
                String[] reportDetails = {dateReport, name, description, quantity, remarks};
                replacementTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}
